CREATE PROCEDURE SP_CREATE_UPDATE_ASSEMBLY_TRANSACTION_QTY
(
 @PART_NUMBER		VARCHAR(50), 
 @LOT_NUMBER 		VARCHAR(50),
 @DATE_IN 		DATETIME,
 @QTY_IN 		INT,
 @VENDOR_NAME 		VARCHAR(50),
 @Fab_Lot_No		varchar(50),
 @DATE_CODE			VARCHAR(50) 
 )
 AS
 BEGIN
	
 	DECLARE @PROCESS_STEP_ID INT
 	DECLARE @LOCATION_ID INT
 	DECLARE @LOT_ID INT
	DECLARE @EXIST_DATE_CODE VARCHAR(50)	
	 	
 	SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.PART_NUMBER = @PART_NUMBER  AND PS_TYPE_NAME = 'Assembly' AND PS_TYPE = 'Assembly' AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'Assembly' AND L_VENDOR_NAME = @VENDOR_NAME
	
	IF @PROCESS_STEP_ID IS NULL
 	BEGIN
		SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = 'Assembly' AND PS_TYPE = 'Assembly' AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
 		IF @PROCESS_STEP_ID IS NULL 
 		BEGIN
			--For Discrepancy Lot Notification Alert
			/*IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = 'Assembly' AND IS_USER_NOTIFIED = 1 ) = 0 
			BEGIN
				INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status,TimeStamp )
					VALUES ( @part_number, @lot_number, @Fab_Lot_No, @vendor_name, 'Assembly','Discrepancy Lot Notification', null, @Qty_In, null, 'Open', CURRENT_TIMESTAMP )
			END */
				
			 -- DUMMY UPDATE TO AVOID SQLEXCEPTION
 			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'

 			RETURN
 		END
	END
	
 
 	SELECT @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
  
 	IF @LOT_ID IS NULL -- Check if the lot exists
 	BEGIN 	
		-- Create Lot
 		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, Wafer_Qty_In, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP, LOT_ENTERED_BY, LOT_CATEGORY, LOT_DROP_OUT, LOT_DATE_CODE) 
				VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @Fab_Lot_No, @LOT_NUMBER, null, @QTY_IN, @DATE_IN, 'Close', CURRENT_TIMESTAMP, 1 , 'PROD', 0 , @DATE_CODE)
		SELECT @LOT_ID = @@IDENTITY

		INSERT INTO LOT_TABLE_HISTORY ( LOT_ID, PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, Wafer_Qty_In, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP, LOT_ENTERED_BY, LOT_CATEGORY, LOT_DROP_OUT, LOT_DATE_CODE,LOT_COMMENTS) 
				VALUES (@LOT_ID, @PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @Fab_Lot_No, @LOT_NUMBER, null, @QTY_IN, @DATE_IN, 'Close', CURRENT_TIMESTAMP,1, 'PROD', 0 , @DATE_CODE, 'Transaction sheet In qty')		

		INSERT INTO ASSEMBLY_LOT (LOT_ID, TIMESTAMP)
			 VALUES (@LOT_ID, CURRENT_TIMESTAMP )

	END
	ELSE
	BEGIN
		SELECT @EXIST_DATE_CODE = LOT_DATE_CODE FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
		IF @EXIST_DATE_CODE IS NULL AND @DATE_CODE IS NOT NULL
		BEGIN
				UPDATE LOT_TABLE SET LOT_DATE_CODE = @DATE_CODE WHERE LOT_ID = @LOT_ID
		END
		IF (SELECT COUNT(*) FROM LOT_TABLE_HISTORY WHERE LOT_ID = @LOT_ID AND LOT_ENTERED_BY = 1 AND  LOT_CURRENT_STATUS = 'Close' ) = 0
		BEGIn
			INSERT INTO LOT_TABLE_HISTORY ( LOT_ID, PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, Wafer_Qty_In, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP, LOT_ENTERED_BY, LOT_CATEGORY, LOT_DROP_OUT, LOT_COMMENTS) 
				VALUES (@LOT_ID, @PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @Fab_Lot_No, @LOT_NUMBER, null, @QTY_IN, @DATE_IN, 'Close', CURRENT_TIMESTAMP,1, 'PROD', 0 , 'Transaction sheet In qty')		
		END
	END

	/*Closing the Previous Transit Lots when ever Lots are came in to Next Process*/
	EXEC SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @LOT_NUMBER, @FAB_LOT_NO, @QTY_IN, @DATE_IN

END